{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.path.append('..')\n",
    "from configure.settings import DBSelector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "client = DBSelector().mongo('qq')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "doc_name = 'xueqiu_private_2024-01-22'\n",
    "data = list(client['db_stock'][doc_name].find())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "627"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.DataFrame(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>priority</th>\n",
       "      <th>symbol</th>\n",
       "      <th>name</th>\n",
       "      <th>manager_nick_name</th>\n",
       "      <th>unit_net_value</th>\n",
       "      <th>end_date</th>\n",
       "      <th>end_date_val</th>\n",
       "      <th>profit_rate</th>\n",
       "      <th>max_drawdown_rate</th>\n",
       "      <th>annual_return_this_year</th>\n",
       "      <th>...</th>\n",
       "      <th>fd_code</th>\n",
       "      <th>has_select</th>\n",
       "      <th>risk_rating</th>\n",
       "      <th>is_open_day_tmp</th>\n",
       "      <th>is_raise</th>\n",
       "      <th>create_time</th>\n",
       "      <th>sharp_rate</th>\n",
       "      <th>half_a_year</th>\n",
       "      <th>crawltime</th>\n",
       "      <th>normal_Item</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-1</td>\n",
       "      <td>P000777</td>\n",
       "      <td>大禾投资-掘金5号</td>\n",
       "      <td>大禾投资</td>\n",
       "      <td>12.8810</td>\n",
       "      <td>01-12</td>\n",
       "      <td>1704988800000</td>\n",
       "      <td>11.8810</td>\n",
       "      <td>0.5787</td>\n",
       "      <td>-0.0389</td>\n",
       "      <td>...</td>\n",
       "      <td>SM6778</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1.09</td>\n",
       "      <td>False</td>\n",
       "      <td>2024-01-21 19:43:49.432</td>\n",
       "      <td>{'nav_type': 'normal', 'max_draw_down': '-0.00...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-1</td>\n",
       "      <td>P001029</td>\n",
       "      <td>仙人掌盈沣远航一号</td>\n",
       "      <td>上海仙人掌</td>\n",
       "      <td>7.5870</td>\n",
       "      <td>01-12</td>\n",
       "      <td>1704988800000</td>\n",
       "      <td>6.5870</td>\n",
       "      <td>0.3798</td>\n",
       "      <td>-0.0928</td>\n",
       "      <td>...</td>\n",
       "      <td>SS5579</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1.09</td>\n",
       "      <td>False</td>\n",
       "      <td>2024-01-21 19:43:49.432</td>\n",
       "      <td>{'nav_type': 'normal', 'max_draw_down': '-0.04...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-1</td>\n",
       "      <td>P000865</td>\n",
       "      <td>达理1号</td>\n",
       "      <td>成于思</td>\n",
       "      <td>7.3130</td>\n",
       "      <td>01-12</td>\n",
       "      <td>1704988800000</td>\n",
       "      <td>6.3130</td>\n",
       "      <td>0.3404</td>\n",
       "      <td>-0.0757</td>\n",
       "      <td>...</td>\n",
       "      <td>SL3433</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1.04</td>\n",
       "      <td>False</td>\n",
       "      <td>2024-01-21 19:43:49.432</td>\n",
       "      <td>{'nav_type': 'normal', 'max_draw_down': '-0.03...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-1</td>\n",
       "      <td>P000946</td>\n",
       "      <td>正圆壹号</td>\n",
       "      <td>正圆投资</td>\n",
       "      <td>4.3460</td>\n",
       "      <td>01-12</td>\n",
       "      <td>1704988800000</td>\n",
       "      <td>6.2330</td>\n",
       "      <td>0.5562</td>\n",
       "      <td>-0.4111</td>\n",
       "      <td>...</td>\n",
       "      <td>SJ5352</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.58</td>\n",
       "      <td>False</td>\n",
       "      <td>2024-01-21 19:43:49.432</td>\n",
       "      <td>{'nav_type': 'normal', 'max_draw_down': '-0.09...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-1</td>\n",
       "      <td>P000531</td>\n",
       "      <td>希瓦小牛1号</td>\n",
       "      <td>梁宏</td>\n",
       "      <td>5.5048</td>\n",
       "      <td>01-12</td>\n",
       "      <td>1704988800000</td>\n",
       "      <td>6.0854</td>\n",
       "      <td>0.4055</td>\n",
       "      <td>-0.0934</td>\n",
       "      <td>...</td>\n",
       "      <td>A00003</td>\n",
       "      <td>True</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.95</td>\n",
       "      <td>False</td>\n",
       "      <td>2024-01-21 19:43:49.432</td>\n",
       "      <td>{'nav_type': 'normal', 'max_draw_down': '-0.00...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   priority   symbol       name manager_nick_name  unit_net_value end_date  \\\n",
       "0        -1  P000777  大禾投资-掘金5号              大禾投资         12.8810    01-12   \n",
       "1        -1  P001029  仙人掌盈沣远航一号             上海仙人掌          7.5870    01-12   \n",
       "2        -1  P000865       达理1号               成于思          7.3130    01-12   \n",
       "3        -1  P000946       正圆壹号              正圆投资          4.3460    01-12   \n",
       "4        -1  P000531     希瓦小牛1号                梁宏          5.5048    01-12   \n",
       "\n",
       "    end_date_val  profit_rate  max_drawdown_rate  annual_return_this_year  \\\n",
       "0  1704988800000      11.8810             0.5787                  -0.0389   \n",
       "1  1704988800000       6.5870             0.3798                  -0.0928   \n",
       "2  1704988800000       6.3130             0.3404                  -0.0757   \n",
       "3  1704988800000       6.2330             0.5562                  -0.4111   \n",
       "4  1704988800000       6.0854             0.4055                  -0.0934   \n",
       "\n",
       "   ...  fd_code  has_select risk_rating  is_open_day_tmp is_raise create_time  \\\n",
       "0  ...   SM6778       False        None             None     None        None   \n",
       "1  ...   SS5579       False        None             None     None        None   \n",
       "2  ...   SL3433       False        None             None     None        None   \n",
       "3  ...   SJ5352       False        None             None     None        None   \n",
       "4  ...   A00003        True        None             None     None        None   \n",
       "\n",
       "  sharp_rate half_a_year               crawltime  \\\n",
       "0       1.09       False 2024-01-21 19:43:49.432   \n",
       "1       1.09       False 2024-01-21 19:43:49.432   \n",
       "2       1.04       False 2024-01-21 19:43:49.432   \n",
       "3       0.58       False 2024-01-21 19:43:49.432   \n",
       "4       0.95       False 2024-01-21 19:43:49.432   \n",
       "\n",
       "                                         normal_Item  \n",
       "0  {'nav_type': 'normal', 'max_draw_down': '-0.00...  \n",
       "1  {'nav_type': 'normal', 'max_draw_down': '-0.04...  \n",
       "2  {'nav_type': 'normal', 'max_draw_down': '-0.03...  \n",
       "3  {'nav_type': 'normal', 'max_draw_down': '-0.09...  \n",
       "4  {'nav_type': 'normal', 'max_draw_down': '-0.00...  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "del df['_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>priority</th>\n",
       "      <th>symbol</th>\n",
       "      <th>name</th>\n",
       "      <th>manager_nick_name</th>\n",
       "      <th>unit_net_value</th>\n",
       "      <th>end_date</th>\n",
       "      <th>end_date_val</th>\n",
       "      <th>profit_rate</th>\n",
       "      <th>max_drawdown_rate</th>\n",
       "      <th>annual_return_this_year</th>\n",
       "      <th>...</th>\n",
       "      <th>fd_code</th>\n",
       "      <th>has_select</th>\n",
       "      <th>risk_rating</th>\n",
       "      <th>is_open_day_tmp</th>\n",
       "      <th>is_raise</th>\n",
       "      <th>create_time</th>\n",
       "      <th>sharp_rate</th>\n",
       "      <th>half_a_year</th>\n",
       "      <th>crawltime</th>\n",
       "      <th>normal_Item</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-1</td>\n",
       "      <td>P000777</td>\n",
       "      <td>大禾投资-掘金5号</td>\n",
       "      <td>大禾投资</td>\n",
       "      <td>12.979</td>\n",
       "      <td>01-19</td>\n",
       "      <td>1705593600000</td>\n",
       "      <td>11.979</td>\n",
       "      <td>0.5787</td>\n",
       "      <td>-0.0779</td>\n",
       "      <td>...</td>\n",
       "      <td>SM6778</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1.09</td>\n",
       "      <td>False</td>\n",
       "      <td>2024-01-22 18:11:32.741</td>\n",
       "      <td>{'nav_type': 'normal', 'max_draw_down': '-0.00...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   priority   symbol       name manager_nick_name  unit_net_value end_date  \\\n",
       "0        -1  P000777  大禾投资-掘金5号              大禾投资          12.979    01-19   \n",
       "\n",
       "    end_date_val  profit_rate  max_drawdown_rate  annual_return_this_year  \\\n",
       "0  1705593600000       11.979             0.5787                  -0.0779   \n",
       "\n",
       "   ...  fd_code  has_select risk_rating  is_open_day_tmp is_raise create_time  \\\n",
       "0  ...   SM6778       False        None             None     None        None   \n",
       "\n",
       "  sharp_rate half_a_year               crawltime  \\\n",
       "0       1.09       False 2024-01-22 18:11:32.741   \n",
       "\n",
       "                                         normal_Item  \n",
       "0  {'nav_type': 'normal', 'max_draw_down': '-0.00...  \n",
       "\n",
       "[1 rows x 22 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['annual_return_this_year']=df['annual_return_this_year'].map(lambda x:100*x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['combine-name']=df['name']+'-'+df['manager_nick_name']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head(5)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "top = df.sort_values('annual_return_this_year',ascending=False).head(20)\n",
    "bottom =  df.sort_values('annual_return_this_year',ascending=True).head(20)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp_data = df[df['symbol']=='P000531']['normal_Item']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4   -0.0934\n",
       "Name: annual_return_this_year, dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['symbol']=='P000531']['annual_return_this_year']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'nav_type': 'normal',\n",
       " 'max_draw_down': '-0.0064',\n",
       " 'sharp': '2.86',\n",
       " 'desc': '本产品',\n",
       " 'rate': '0.0082'}"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp_data.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "def this_year_actual_return(x):\n",
    "    # item = x['normal_Item']\n",
    "    return float(x['rate'])*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['this_year_actual_return'] = df['normal_Item'].map(this_year_actual_return)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel('xueqiu_private_fund.xlsx',encoding='utf8',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "top = df.sort_values('this_year_actual_return',ascending=False).head(20)\n",
    "bottom =  df.sort_values('this_year_actual_return',ascending=True).head(20)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>priority</th>\n",
       "      <th>symbol</th>\n",
       "      <th>name</th>\n",
       "      <th>manager_nick_name</th>\n",
       "      <th>unit_net_value</th>\n",
       "      <th>end_date</th>\n",
       "      <th>end_date_val</th>\n",
       "      <th>profit_rate</th>\n",
       "      <th>max_drawdown_rate</th>\n",
       "      <th>annual_return_this_year</th>\n",
       "      <th>...</th>\n",
       "      <th>has_select</th>\n",
       "      <th>risk_rating</th>\n",
       "      <th>is_open_day_tmp</th>\n",
       "      <th>is_raise</th>\n",
       "      <th>create_time</th>\n",
       "      <th>sharp_rate</th>\n",
       "      <th>half_a_year</th>\n",
       "      <th>crawltime</th>\n",
       "      <th>normal_Item</th>\n",
       "      <th>this_year_actual_return</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-1</td>\n",
       "      <td>P000777</td>\n",
       "      <td>大禾投资-掘金5号</td>\n",
       "      <td>大禾投资</td>\n",
       "      <td>12.8810</td>\n",
       "      <td>01-12</td>\n",
       "      <td>1704988800000</td>\n",
       "      <td>11.8810</td>\n",
       "      <td>0.5787</td>\n",
       "      <td>-0.0389</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1.09</td>\n",
       "      <td>False</td>\n",
       "      <td>2024-01-21 19:43:49.432</td>\n",
       "      <td>{'nav_type': 'normal', 'max_draw_down': '-0.00...</td>\n",
       "      <td>0.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-1</td>\n",
       "      <td>P001029</td>\n",
       "      <td>仙人掌盈沣远航一号</td>\n",
       "      <td>上海仙人掌</td>\n",
       "      <td>7.5870</td>\n",
       "      <td>01-12</td>\n",
       "      <td>1704988800000</td>\n",
       "      <td>6.5870</td>\n",
       "      <td>0.3798</td>\n",
       "      <td>-0.0928</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1.09</td>\n",
       "      <td>False</td>\n",
       "      <td>2024-01-21 19:43:49.432</td>\n",
       "      <td>{'nav_type': 'normal', 'max_draw_down': '-0.04...</td>\n",
       "      <td>-4.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-1</td>\n",
       "      <td>P000865</td>\n",
       "      <td>达理1号</td>\n",
       "      <td>成于思</td>\n",
       "      <td>7.3130</td>\n",
       "      <td>01-12</td>\n",
       "      <td>1704988800000</td>\n",
       "      <td>6.3130</td>\n",
       "      <td>0.3404</td>\n",
       "      <td>-0.0757</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1.04</td>\n",
       "      <td>False</td>\n",
       "      <td>2024-01-21 19:43:49.432</td>\n",
       "      <td>{'nav_type': 'normal', 'max_draw_down': '-0.03...</td>\n",
       "      <td>-1.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-1</td>\n",
       "      <td>P000946</td>\n",
       "      <td>正圆壹号</td>\n",
       "      <td>正圆投资</td>\n",
       "      <td>4.3460</td>\n",
       "      <td>01-12</td>\n",
       "      <td>1704988800000</td>\n",
       "      <td>6.2330</td>\n",
       "      <td>0.5562</td>\n",
       "      <td>-0.4111</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.58</td>\n",
       "      <td>False</td>\n",
       "      <td>2024-01-21 19:43:49.432</td>\n",
       "      <td>{'nav_type': 'normal', 'max_draw_down': '-0.09...</td>\n",
       "      <td>-9.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-1</td>\n",
       "      <td>P000531</td>\n",
       "      <td>希瓦小牛1号</td>\n",
       "      <td>梁宏</td>\n",
       "      <td>5.5048</td>\n",
       "      <td>01-12</td>\n",
       "      <td>1704988800000</td>\n",
       "      <td>6.0854</td>\n",
       "      <td>0.4055</td>\n",
       "      <td>-0.0934</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.95</td>\n",
       "      <td>False</td>\n",
       "      <td>2024-01-21 19:43:49.432</td>\n",
       "      <td>{'nav_type': 'normal', 'max_draw_down': '-0.00...</td>\n",
       "      <td>0.82</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   priority   symbol       name manager_nick_name  unit_net_value end_date  \\\n",
       "0        -1  P000777  大禾投资-掘金5号              大禾投资         12.8810    01-12   \n",
       "1        -1  P001029  仙人掌盈沣远航一号             上海仙人掌          7.5870    01-12   \n",
       "2        -1  P000865       达理1号               成于思          7.3130    01-12   \n",
       "3        -1  P000946       正圆壹号              正圆投资          4.3460    01-12   \n",
       "4        -1  P000531     希瓦小牛1号                梁宏          5.5048    01-12   \n",
       "\n",
       "    end_date_val  profit_rate  max_drawdown_rate  annual_return_this_year  \\\n",
       "0  1704988800000      11.8810             0.5787                  -0.0389   \n",
       "1  1704988800000       6.5870             0.3798                  -0.0928   \n",
       "2  1704988800000       6.3130             0.3404                  -0.0757   \n",
       "3  1704988800000       6.2330             0.5562                  -0.4111   \n",
       "4  1704988800000       6.0854             0.4055                  -0.0934   \n",
       "\n",
       "   ...  has_select  risk_rating is_open_day_tmp  is_raise create_time  \\\n",
       "0  ...       False         None            None      None        None   \n",
       "1  ...       False         None            None      None        None   \n",
       "2  ...       False         None            None      None        None   \n",
       "3  ...       False         None            None      None        None   \n",
       "4  ...        True         None            None      None        None   \n",
       "\n",
       "  sharp_rate half_a_year               crawltime  \\\n",
       "0       1.09       False 2024-01-21 19:43:49.432   \n",
       "1       1.09       False 2024-01-21 19:43:49.432   \n",
       "2       1.04       False 2024-01-21 19:43:49.432   \n",
       "3       0.58       False 2024-01-21 19:43:49.432   \n",
       "4       0.95       False 2024-01-21 19:43:49.432   \n",
       "\n",
       "                                         normal_Item  this_year_actual_return  \n",
       "0  {'nav_type': 'normal', 'max_draw_down': '-0.00...                     0.76  \n",
       "1  {'nav_type': 'normal', 'max_draw_down': '-0.04...                    -4.43  \n",
       "2  {'nav_type': 'normal', 'max_draw_down': '-0.03...                    -1.96  \n",
       "3  {'nav_type': 'normal', 'max_draw_down': '-0.09...                    -9.04  \n",
       "4  {'nav_type': 'normal', 'max_draw_down': '-0.00...                     0.82  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 绘图部分\n",
    "from pyecharts import options as opts\n",
    "from pyecharts.charts import Bar\n",
    "from pyecharts.globals import ThemeType\n",
    "\n",
    "# simple_result ={}\n",
    "# for k,v in industry_dict.items():\n",
    "#     simple_result[k]=round(v['premium_rt'].mean(),0)\n",
    "\n",
    "# simple_result = list(sorted(simple_result.items(),key=lambda x:x[1],reverse=True))\n",
    "# X=[i[0] for i in simple_result]\n",
    "# Y=[i[1] for i in simple_result]\n",
    "X=top['name'].tolist()\n",
    "Y=top['this_year_actual_return'].tolist()\n",
    "Y=[round(i) for i in Y]\n",
    "c = (\n",
    "    Bar()\n",
    "    .add_xaxis(X)\n",
    "    .add_yaxis(y_axis=Y,series_name='收益率%',\n",
    "    category_gap=\"70%\"\n",
    "    )\n",
    "    # .reversal_axis()\n",
    "    .set_series_opts(label_opts=opts.LabelOpts(position=\"right\"))\n",
    "    .set_global_opts(title_opts=opts.TitleOpts(title=\"雪球私募收益率2024年1-月排名前20\"),\n",
    "    xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=45)),\n",
    "      yaxis_opts=opts.AxisOpts(splitline_opts=opts.SplitLineOpts(is_show=True),),\n",
    "    # datazoom_opts=opts.DataZoomOpts()\n",
    "    )\n",
    "    # .render(\"../plot_image/私募收益率靠前.html\")\n",
    ")\n",
    "\n",
    "from pyecharts.charts import Bar, Grid, Line\n",
    "grid = (\n",
    "    Grid(init_opts=opts.InitOpts(theme= ThemeType.CHALK))\n",
    "    .add(c, grid_opts=opts.GridOpts(pos_bottom=\"25%\"))\n",
    "    .render(\"../plot_image/私募收益率靠前top-20.html\")\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-4.24518341307815"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['this_year_actual_return'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-4.24"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['this_year_actual_return'].median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-22.439999999999998"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['end_date']>='01-19']['this_year_actual_return'].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "375"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['end_date']>='01-19']['this_year_actual_return'].argmin()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "594"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['this_year_actual_return'].argmin()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "priority                                                                  -1\n",
       "symbol                                                               P000685\n",
       "name                                                              上海斯诺波-爱久量化\n",
       "manager_nick_name                                                       爱久投资\n",
       "unit_net_value                                                         0.736\n",
       "end_date                                                               01-19\n",
       "end_date_val                                                   1705593600000\n",
       "profit_rate                                                           -0.264\n",
       "max_drawdown_rate                                                     0.5482\n",
       "annual_return_this_year                                               -45.76\n",
       "annual_return_year                                                   -0.4576\n",
       "show_profit_status                                                         1\n",
       "fd_code                                                               SJV254\n",
       "has_select                                                             False\n",
       "risk_rating                                                             None\n",
       "is_open_day_tmp                                                         None\n",
       "is_raise                                                                None\n",
       "create_time                                                             None\n",
       "sharp_rate                                                             -0.28\n",
       "half_a_year                                                            False\n",
       "crawltime                                         2024-01-22 18:11:32.742000\n",
       "normal_Item                {'nav_type': 'normal', 'max_draw_down': '-0.22...\n",
       "this_year_actual_return                                               -22.44\n",
       "Name: 594, dtype: object"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[594]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "X=bottom['name'].tolist()\n",
    "Y=bottom['this_year_actual_return'].tolist()\n",
    "Y=[round(i) for i in Y]\n",
    "c = (\n",
    "    Bar()\n",
    "    .add_xaxis(X)\n",
    "    .add_yaxis(y_axis=Y,series_name='收益率%',\n",
    "    category_gap=\"70%\"\n",
    "    )\n",
    "    .set_series_opts(label_opts=opts.LabelOpts(position=\"right\"))\n",
    "    .set_global_opts(title_opts=opts.TitleOpts(title=\"雪球私募收益率2024年1月排名倒数20\"),\n",
    "    xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=45),splitline_opts=opts.SplitLineOpts(is_show=False),),\n",
    "    yaxis_opts=opts.AxisOpts(splitline_opts=opts.SplitLineOpts(is_show=True),),\n",
    "    )\n",
    ")\n",
    "\n",
    "from pyecharts.charts import Bar, Grid, Line\n",
    "grid = (\n",
    "    Grid(init_opts=opts.InitOpts(theme= ThemeType.CHALK))\n",
    "    .add(c, grid_opts=opts.GridOpts(pos_bottom=\"25%\"))\n",
    "    .render(\"../plot_image/私募收益率靠后-tail.html\")\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['max_drawdown_rate']=df['max_drawdown_rate'].map(lambda x:x*100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['max_drawdown_rate']=df['max_drawdown_rate'].map(lambda x:x*100)\n",
    "top__ = df.sort_values('annual_return_this_year',ascending=False).head(50)\n",
    "bottom__ =  df.sort_values('annual_return_this_year',ascending=True).head(50)\n",
    "top_=top__.rename(columns={'name':'私募名称','manager_nick_name':'管理人','annual_return_this_year':'今年收益率','unit_net_value':'单位净值','max_drawdown_rate':'最大回撤'})\n",
    "# top[['name','manager_nick_name','annual_return_this_year','unit_net_value']].head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "top_[['私募名称','管理人','今年收益率','单位净值','最大回撤']].reset_index(drop=True).head(50).to_excel('top.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "bottom_=bottom__.rename(columns={'name':'私募名称','manager_nick_name':'管理人','annual_return_this_year':'今年收益率','unit_net_value':'单位净值','max_drawdown_rate':'最大回撤'})\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "bottom_[['私募名称','管理人','今年收益率','单位净值','最大回撤']].reset_index(drop=True).head(50).to_excel('bottom.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 合并\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_full = list(client['db_stock']['xueqiu_private_2022-08-18_brute_force'].find())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_full_df = pd.DataFrame(data_full)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>symbol</th>\n",
       "      <th>name</th>\n",
       "      <th>profit_rate</th>\n",
       "      <th>annual_return_this_year</th>\n",
       "      <th>manager_nick_name</th>\n",
       "      <th>netvalue_date</th>\n",
       "      <th>create_date</th>\n",
       "      <th>close_status</th>\n",
       "      <th>crwaltime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>62fdfcde3662f56a59ea53cd</td>\n",
       "      <td>P000030</td>\n",
       "      <td>私募工场(格顿1期)</td>\n",
       "      <td>58.15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2015-11-13</td>\n",
       "      <td>2014-01-17</td>\n",
       "      <td>True</td>\n",
       "      <td>2022-08-18 16:48:30.167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>62fdfcde3662f56a59ea53ce</td>\n",
       "      <td>P000031</td>\n",
       "      <td>私募工场(英菲证券)</td>\n",
       "      <td>90.44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2015-10-21</td>\n",
       "      <td>2014-01-17</td>\n",
       "      <td>True</td>\n",
       "      <td>2022-08-18 16:48:30.532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>62fdfcdf3662f56a59ea53cf</td>\n",
       "      <td>P000032</td>\n",
       "      <td>私募工场(思考8号)</td>\n",
       "      <td>11.47</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2014-09-16</td>\n",
       "      <td>2014-01-20</td>\n",
       "      <td>True</td>\n",
       "      <td>2022-08-18 16:48:31.036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>62fdfcdf3662f56a59ea53d0</td>\n",
       "      <td>P000033</td>\n",
       "      <td>私募工场(曦微成长精选1期)</td>\n",
       "      <td>85.72</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2015-12-08</td>\n",
       "      <td>2014-02-20</td>\n",
       "      <td>True</td>\n",
       "      <td>2022-08-18 16:48:31.380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>62fdfceb3662f56a59ea53d1</td>\n",
       "      <td>P000048</td>\n",
       "      <td>私募工场(以恒1号)</td>\n",
       "      <td>82.33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-08-24</td>\n",
       "      <td>2014-01-17</td>\n",
       "      <td>True</td>\n",
       "      <td>2022-08-18 16:48:43.106</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id   symbol            name  profit_rate  \\\n",
       "0  62fdfcde3662f56a59ea53cd  P000030      私募工场(格顿1期)        58.15   \n",
       "1  62fdfcde3662f56a59ea53ce  P000031      私募工场(英菲证券)        90.44   \n",
       "2  62fdfcdf3662f56a59ea53cf  P000032      私募工场(思考8号)        11.47   \n",
       "3  62fdfcdf3662f56a59ea53d0  P000033  私募工场(曦微成长精选1期)        85.72   \n",
       "4  62fdfceb3662f56a59ea53d1  P000048      私募工场(以恒1号)        82.33   \n",
       "\n",
       "   annual_return_this_year manager_nick_name netvalue_date create_date  \\\n",
       "0                      NaN              None    2015-11-13  2014-01-17   \n",
       "1                      NaN              None    2015-10-21  2014-01-17   \n",
       "2                      NaN              None    2014-09-16  2014-01-20   \n",
       "3                      NaN              None    2015-12-08  2014-02-20   \n",
       "4                      NaN              None    2016-08-24  2014-01-17   \n",
       "\n",
       "   close_status               crwaltime  \n",
       "0          True 2022-08-18 16:48:30.167  \n",
       "1          True 2022-08-18 16:48:30.532  \n",
       "2          True 2022-08-18 16:48:31.036  \n",
       "3          True 2022-08-18 16:48:31.380  \n",
       "4          True 2022-08-18 16:48:43.106  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_full_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "del data_full_df['_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "merge_df = pd.merge(df,data_full_df,left_on='symbol',right_on='symbol',how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>priority</th>\n",
       "      <th>symbol</th>\n",
       "      <th>name_x</th>\n",
       "      <th>manager_nick_name_x</th>\n",
       "      <th>unit_net_value</th>\n",
       "      <th>end_date</th>\n",
       "      <th>end_date_val</th>\n",
       "      <th>profit_rate_x</th>\n",
       "      <th>max_drawdown_rate</th>\n",
       "      <th>...</th>\n",
       "      <th>half_a_year</th>\n",
       "      <th>crawltime</th>\n",
       "      <th>name_y</th>\n",
       "      <th>profit_rate_y</th>\n",
       "      <th>annual_return_this_year_y</th>\n",
       "      <th>manager_nick_name_y</th>\n",
       "      <th>netvalue_date</th>\n",
       "      <th>create_date</th>\n",
       "      <th>close_status</th>\n",
       "      <th>crwaltime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>63063461e1b8ad401d34c646</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>P000946</td>\n",
       "      <td>正圆壹号</td>\n",
       "      <td>正圆投资</td>\n",
       "      <td>9.4850</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>11.3720</td>\n",
       "      <td>0.3664</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>正圆壹号</td>\n",
       "      <td>1067.60</td>\n",
       "      <td>8.90</td>\n",
       "      <td>正圆投资</td>\n",
       "      <td>2022-08-12</td>\n",
       "      <td>2016-07-14</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 17:02:13.945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>63063461e1b8ad401d34c647</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>P000777</td>\n",
       "      <td>大禾投资-掘金5号</td>\n",
       "      <td>大禾投资</td>\n",
       "      <td>10.7110</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>9.7110</td>\n",
       "      <td>0.4272</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>大禾投资-掘金5号</td>\n",
       "      <td>958.50</td>\n",
       "      <td>-17.26</td>\n",
       "      <td>大禾投资</td>\n",
       "      <td>2022-08-12</td>\n",
       "      <td>2016-10-12</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 17:00:04.894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>63063461e1b8ad401d34c648</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>P001029</td>\n",
       "      <td>仙人掌盈沣远航一号</td>\n",
       "      <td>上海仙人掌</td>\n",
       "      <td>10.6380</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>9.6380</td>\n",
       "      <td>0.2797</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>仙人掌盈沣远航一号</td>\n",
       "      <td>953.70</td>\n",
       "      <td>-9.30</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-08-12</td>\n",
       "      <td>2017-04-12</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 17:02:57.366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>63063461e1b8ad401d34c649</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>P000865</td>\n",
       "      <td>达理1号</td>\n",
       "      <td>成于思</td>\n",
       "      <td>7.8079</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>6.8079</td>\n",
       "      <td>0.3404</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>达理1号</td>\n",
       "      <td>701.71</td>\n",
       "      <td>-15.58</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-08-12</td>\n",
       "      <td>2016-07-28</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 17:01:17.810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>63063461e1b8ad401d34c64a</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>P000531</td>\n",
       "      <td>希瓦小牛1号</td>\n",
       "      <td>梁宏</td>\n",
       "      <td>5.0318</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>5.6124</td>\n",
       "      <td>0.3726</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>希瓦小牛1号</td>\n",
       "      <td>569.80</td>\n",
       "      <td>-12.08</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-08-12</td>\n",
       "      <td>2015-04-21</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 16:55:06.650</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        _id  priority   symbol     name_x manager_nick_name_x  \\\n",
       "0  63063461e1b8ad401d34c646      -1.0  P000946       正圆壹号                正圆投资   \n",
       "1  63063461e1b8ad401d34c647      -1.0  P000777  大禾投资-掘金5号                大禾投资   \n",
       "2  63063461e1b8ad401d34c648      -1.0  P001029  仙人掌盈沣远航一号               上海仙人掌   \n",
       "3  63063461e1b8ad401d34c649      -1.0  P000865       达理1号                 成于思   \n",
       "4  63063461e1b8ad401d34c64a      -1.0  P000531     希瓦小牛1号                  梁宏   \n",
       "\n",
       "   unit_net_value end_date  end_date_val  profit_rate_x  max_drawdown_rate  \\\n",
       "0          9.4850    08-19  1.660838e+12        11.3720             0.3664   \n",
       "1         10.7110    08-19  1.660838e+12         9.7110             0.4272   \n",
       "2         10.6380    08-19  1.660838e+12         9.6380             0.2797   \n",
       "3          7.8079    08-19  1.660838e+12         6.8079             0.3404   \n",
       "4          5.0318    08-19  1.660838e+12         5.6124             0.3726   \n",
       "\n",
       "   ...  half_a_year               crawltime     name_y profit_rate_y  \\\n",
       "0  ...        False 2022-08-24 22:23:29.646       正圆壹号       1067.60   \n",
       "1  ...        False 2022-08-24 22:23:29.646  大禾投资-掘金5号        958.50   \n",
       "2  ...        False 2022-08-24 22:23:29.646  仙人掌盈沣远航一号        953.70   \n",
       "3  ...        False 2022-08-24 22:23:29.646       达理1号        701.71   \n",
       "4  ...        False 2022-08-24 22:23:29.646     希瓦小牛1号        569.80   \n",
       "\n",
       "  annual_return_this_year_y manager_nick_name_y netvalue_date create_date  \\\n",
       "0                      8.90                正圆投资    2022-08-12  2016-07-14   \n",
       "1                    -17.26                大禾投资    2022-08-12  2016-10-12   \n",
       "2                     -9.30                None    2022-08-12  2017-04-12   \n",
       "3                    -15.58                None    2022-08-12  2016-07-28   \n",
       "4                    -12.08                None    2022-08-12  2015-04-21   \n",
       "\n",
       "  close_status               crwaltime  \n",
       "0        False 2022-08-18 17:02:13.945  \n",
       "1        False 2022-08-18 17:00:04.894  \n",
       "2        False 2022-08-18 17:02:57.366  \n",
       "3        False 2022-08-18 17:01:17.810  \n",
       "4        False 2022-08-18 16:55:06.650  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "del merge_df['_id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "del merge_df['priority']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'merge_df' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m/home/xda/github/stock/analysis/雪球私募收益率分析.ipynb Cell 34\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> <a href='vscode-notebook-cell:/home/xda/github/stock/analysis/%E9%9B%AA%E7%90%83%E7%A7%81%E5%8B%9F%E6%94%B6%E7%9B%8A%E7%8E%87%E5%88%86%E6%9E%90.ipynb#X40sZmlsZQ%3D%3D?line=0'>1</a>\u001b[0m merge_df\u001b[39m.\u001b[39mhead()\n",
      "\u001b[0;31mNameError\u001b[0m: name 'merge_df' is not defined"
     ]
    }
   ],
   "source": [
    "merge_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1016"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(merge_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>symbol</th>\n",
       "      <th>name_x</th>\n",
       "      <th>manager_nick_name_x</th>\n",
       "      <th>unit_net_value</th>\n",
       "      <th>end_date</th>\n",
       "      <th>end_date_val</th>\n",
       "      <th>profit_rate_x</th>\n",
       "      <th>max_drawdown_rate</th>\n",
       "      <th>annual_return_this_year_x</th>\n",
       "      <th>annual_return_year</th>\n",
       "      <th>...</th>\n",
       "      <th>half_a_year</th>\n",
       "      <th>crawltime</th>\n",
       "      <th>name_y</th>\n",
       "      <th>profit_rate_y</th>\n",
       "      <th>annual_return_this_year_y</th>\n",
       "      <th>manager_nick_name_y</th>\n",
       "      <th>netvalue_date</th>\n",
       "      <th>create_date</th>\n",
       "      <th>close_status</th>\n",
       "      <th>crwaltime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>P666012</td>\n",
       "      <td>青骊长兴</td>\n",
       "      <td>青骊投资</td>\n",
       "      <td>4.9590</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>3.9590</td>\n",
       "      <td>0.3768</td>\n",
       "      <td>0.0897</td>\n",
       "      <td>0.1405</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>P666011</td>\n",
       "      <td>青骊长川</td>\n",
       "      <td>青骊投资</td>\n",
       "      <td>3.4640</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>2.4640</td>\n",
       "      <td>0.0988</td>\n",
       "      <td>0.0900</td>\n",
       "      <td>0.1028</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>P666013</td>\n",
       "      <td>兆天金牛精选2号</td>\n",
       "      <td>兆天投资</td>\n",
       "      <td>3.0900</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>2.3900</td>\n",
       "      <td>0.3670</td>\n",
       "      <td>-0.0888</td>\n",
       "      <td>-0.2075</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>P666007</td>\n",
       "      <td>赫富500指数增强一号</td>\n",
       "      <td>赫富投资</td>\n",
       "      <td>2.6480</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>1.6480</td>\n",
       "      <td>0.2749</td>\n",
       "      <td>-0.0123</td>\n",
       "      <td>0.0157</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>P666023</td>\n",
       "      <td>相聚畅联一期</td>\n",
       "      <td>相聚资本</td>\n",
       "      <td>2.0545</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>1.0545</td>\n",
       "      <td>0.2897</td>\n",
       "      <td>0.0376</td>\n",
       "      <td>0.1335</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>P666009</td>\n",
       "      <td>启林正兴东绣1号</td>\n",
       "      <td>启林投资</td>\n",
       "      <td>0.9780</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>1.0040</td>\n",
       "      <td>0.2642</td>\n",
       "      <td>-0.0190</td>\n",
       "      <td>0.0255</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>P666019</td>\n",
       "      <td>悟空对冲量化11期</td>\n",
       "      <td>深圳悟空投资</td>\n",
       "      <td>1.7916</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>0.7916</td>\n",
       "      <td>0.2364</td>\n",
       "      <td>-0.1979</td>\n",
       "      <td>-0.1445</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>P666020</td>\n",
       "      <td>翼虎成长23期</td>\n",
       "      <td>翼虎投资余定恒</td>\n",
       "      <td>1.4470</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>0.7034</td>\n",
       "      <td>0.2959</td>\n",
       "      <td>-0.2587</td>\n",
       "      <td>-0.1961</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>P101729</td>\n",
       "      <td>圆球财富增值计划FOF</td>\n",
       "      <td>私募FOF研究</td>\n",
       "      <td>1.6790</td>\n",
       "      <td>08-22</td>\n",
       "      <td>1.661098e+12</td>\n",
       "      <td>0.6790</td>\n",
       "      <td>0.5277</td>\n",
       "      <td>-0.2834</td>\n",
       "      <td>-0.3085</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223</th>\n",
       "      <td>P666003</td>\n",
       "      <td>雪球私募精英荟3号偏股型FOF</td>\n",
       "      <td>私募FOF研究</td>\n",
       "      <td>1.5010</td>\n",
       "      <td>08-22</td>\n",
       "      <td>1.661098e+12</td>\n",
       "      <td>0.5010</td>\n",
       "      <td>0.3604</td>\n",
       "      <td>-0.1973</td>\n",
       "      <td>-0.2050</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231</th>\n",
       "      <td>P666004</td>\n",
       "      <td>玖鹏稳健成长7号A</td>\n",
       "      <td>玖鹏资产</td>\n",
       "      <td>1.4820</td>\n",
       "      <td>08-22</td>\n",
       "      <td>1.661098e+12</td>\n",
       "      <td>0.4820</td>\n",
       "      <td>0.3834</td>\n",
       "      <td>-0.0789</td>\n",
       "      <td>-0.1434</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241</th>\n",
       "      <td>P666018</td>\n",
       "      <td>翼虎成长18期</td>\n",
       "      <td>翼虎投资余定恒</td>\n",
       "      <td>1.4280</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>0.4280</td>\n",
       "      <td>0.2848</td>\n",
       "      <td>-0.2547</td>\n",
       "      <td>-0.2049</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242</th>\n",
       "      <td>P666016</td>\n",
       "      <td>青骊长川3号</td>\n",
       "      <td>青骊投资</td>\n",
       "      <td>1.2360</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>0.4280</td>\n",
       "      <td>0.0879</td>\n",
       "      <td>0.1046</td>\n",
       "      <td>0.1226</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243</th>\n",
       "      <td>P007128</td>\n",
       "      <td>飞龙FOF</td>\n",
       "      <td>None</td>\n",
       "      <td>1.4260</td>\n",
       "      <td>08-22</td>\n",
       "      <td>1.661098e+12</td>\n",
       "      <td>0.4260</td>\n",
       "      <td>0.3544</td>\n",
       "      <td>-0.1856</td>\n",
       "      <td>-0.1781</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>246</th>\n",
       "      <td>P123456</td>\n",
       "      <td>厚雪长坡</td>\n",
       "      <td>私募FOF研究</td>\n",
       "      <td>1.4210</td>\n",
       "      <td>08-22</td>\n",
       "      <td>1.661098e+12</td>\n",
       "      <td>0.4210</td>\n",
       "      <td>0.4841</td>\n",
       "      <td>-0.2790</td>\n",
       "      <td>-0.2958</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>277</th>\n",
       "      <td>P001272</td>\n",
       "      <td>殊馥馥源套利1号</td>\n",
       "      <td>殊馥投资</td>\n",
       "      <td>1.0018</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>0.3028</td>\n",
       "      <td>0.0147</td>\n",
       "      <td>0.0283</td>\n",
       "      <td>0.0396</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>285</th>\n",
       "      <td>P666021</td>\n",
       "      <td>熙德博远医疗优选1号</td>\n",
       "      <td>李秋实的投资修行</td>\n",
       "      <td>0.8060</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>0.2770</td>\n",
       "      <td>0.3162</td>\n",
       "      <td>-0.0562</td>\n",
       "      <td>-0.1213</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>P666014</td>\n",
       "      <td>汇升星泰FOF一号</td>\n",
       "      <td>汇鸿汇升投资</td>\n",
       "      <td>1.0741</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>0.0741</td>\n",
       "      <td>0.0579</td>\n",
       "      <td>-0.0089</td>\n",
       "      <td>0.0170</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <td>P666025</td>\n",
       "      <td>博孚利CTA-FOF1A号</td>\n",
       "      <td>wenson文灏</td>\n",
       "      <td>1.0688</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>0.0688</td>\n",
       "      <td>0.0619</td>\n",
       "      <td>0.0310</td>\n",
       "      <td>0.0867</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>362</th>\n",
       "      <td>P666027</td>\n",
       "      <td>博孚利CTA-FOF5A号</td>\n",
       "      <td>wenson文灏</td>\n",
       "      <td>1.0650</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>0.0650</td>\n",
       "      <td>0.0770</td>\n",
       "      <td>0.0503</td>\n",
       "      <td>0.1071</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>417</th>\n",
       "      <td>P001273</td>\n",
       "      <td>仲阳博观量化选股1号1期</td>\n",
       "      <td>仲阳天王星</td>\n",
       "      <td>0.9995</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>-0.0005</td>\n",
       "      <td>0.0005</td>\n",
       "      <td>-0.0005</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>420</th>\n",
       "      <td>P001271</td>\n",
       "      <td>殊馥馥荣2号</td>\n",
       "      <td>殊馥投资</td>\n",
       "      <td>0.9987</td>\n",
       "      <td>08-22</td>\n",
       "      <td>1.661098e+12</td>\n",
       "      <td>-0.0013</td>\n",
       "      <td>0.0060</td>\n",
       "      <td>-0.0013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>485</th>\n",
       "      <td>P666010</td>\n",
       "      <td>熙德博远医疗优选3号A</td>\n",
       "      <td>李秋实的投资修行</td>\n",
       "      <td>0.8560</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>-0.1440</td>\n",
       "      <td>0.2532</td>\n",
       "      <td>-0.0404</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493</th>\n",
       "      <td>P666024</td>\n",
       "      <td>悟空11期8号</td>\n",
       "      <td>深圳悟空投资</td>\n",
       "      <td>0.8203</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1.660838e+12</td>\n",
       "      <td>-0.1797</td>\n",
       "      <td>0.2261</td>\n",
       "      <td>-0.2078</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>511</th>\n",
       "      <td>P666005</td>\n",
       "      <td>华宝证券华量安瑞1号FOF集合资产管理计划</td>\n",
       "      <td>华宝资管总部</td>\n",
       "      <td>1.2393</td>\n",
       "      <td>08-02</td>\n",
       "      <td>1.659370e+12</td>\n",
       "      <td>0.2393</td>\n",
       "      <td>0.0472</td>\n",
       "      <td>0.0422</td>\n",
       "      <td>0.0424</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:41.368</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514</th>\n",
       "      <td>P666017</td>\n",
       "      <td>洛书裕和建安</td>\n",
       "      <td>洛书投资</td>\n",
       "      <td>1.1747</td>\n",
       "      <td>07-29</td>\n",
       "      <td>1.659024e+12</td>\n",
       "      <td>0.1747</td>\n",
       "      <td>0.1376</td>\n",
       "      <td>-0.0613</td>\n",
       "      <td>0.0256</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:41.368</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>26 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      symbol                 name_x manager_nick_name_x  unit_net_value  \\\n",
       "11   P666012                   青骊长兴                青骊投资          4.9590   \n",
       "23   P666011                   青骊长川                青骊投资          3.4640   \n",
       "24   P666013               兆天金牛精选2号                兆天投资          3.0900   \n",
       "60   P666007            赫富500指数增强一号                赫富投资          2.6480   \n",
       "117  P666023                 相聚畅联一期                相聚资本          2.0545   \n",
       "123  P666009               启林正兴东绣1号                启林投资          0.9780   \n",
       "161  P666019              悟空对冲量化11期              深圳悟空投资          1.7916   \n",
       "181  P666020                翼虎成长23期             翼虎投资余定恒          1.4470   \n",
       "185  P101729            圆球财富增值计划FOF             私募FOF研究          1.6790   \n",
       "223  P666003        雪球私募精英荟3号偏股型FOF             私募FOF研究          1.5010   \n",
       "231  P666004              玖鹏稳健成长7号A                玖鹏资产          1.4820   \n",
       "241  P666018                翼虎成长18期             翼虎投资余定恒          1.4280   \n",
       "242  P666016                 青骊长川3号                青骊投资          1.2360   \n",
       "243  P007128                  飞龙FOF                None          1.4260   \n",
       "246  P123456                   厚雪长坡             私募FOF研究          1.4210   \n",
       "277  P001272               殊馥馥源套利1号                殊馥投资          1.0018   \n",
       "285  P666021             熙德博远医疗优选1号            李秋实的投资修行          0.8060   \n",
       "357  P666014              汇升星泰FOF一号              汇鸿汇升投资          1.0741   \n",
       "360  P666025          博孚利CTA-FOF1A号            wenson文灏          1.0688   \n",
       "362  P666027          博孚利CTA-FOF5A号            wenson文灏          1.0650   \n",
       "417  P001273           仲阳博观量化选股1号1期               仲阳天王星          0.9995   \n",
       "420  P001271                 殊馥馥荣2号                殊馥投资          0.9987   \n",
       "485  P666010            熙德博远医疗优选3号A            李秋实的投资修行          0.8560   \n",
       "493  P666024                悟空11期8号              深圳悟空投资          0.8203   \n",
       "511  P666005  华宝证券华量安瑞1号FOF集合资产管理计划              华宝资管总部          1.2393   \n",
       "514  P666017                 洛书裕和建安                洛书投资          1.1747   \n",
       "\n",
       "    end_date  end_date_val  profit_rate_x  max_drawdown_rate  \\\n",
       "11     08-19  1.660838e+12         3.9590             0.3768   \n",
       "23     08-19  1.660838e+12         2.4640             0.0988   \n",
       "24     08-19  1.660838e+12         2.3900             0.3670   \n",
       "60     08-19  1.660838e+12         1.6480             0.2749   \n",
       "117    08-19  1.660838e+12         1.0545             0.2897   \n",
       "123    08-19  1.660838e+12         1.0040             0.2642   \n",
       "161    08-19  1.660838e+12         0.7916             0.2364   \n",
       "181    08-19  1.660838e+12         0.7034             0.2959   \n",
       "185    08-22  1.661098e+12         0.6790             0.5277   \n",
       "223    08-22  1.661098e+12         0.5010             0.3604   \n",
       "231    08-22  1.661098e+12         0.4820             0.3834   \n",
       "241    08-19  1.660838e+12         0.4280             0.2848   \n",
       "242    08-19  1.660838e+12         0.4280             0.0879   \n",
       "243    08-22  1.661098e+12         0.4260             0.3544   \n",
       "246    08-22  1.661098e+12         0.4210             0.4841   \n",
       "277    08-19  1.660838e+12         0.3028             0.0147   \n",
       "285    08-19  1.660838e+12         0.2770             0.3162   \n",
       "357    08-19  1.660838e+12         0.0741             0.0579   \n",
       "360    08-19  1.660838e+12         0.0688             0.0619   \n",
       "362    08-19  1.660838e+12         0.0650             0.0770   \n",
       "417    08-19  1.660838e+12        -0.0005             0.0005   \n",
       "420    08-22  1.661098e+12        -0.0013             0.0060   \n",
       "485    08-19  1.660838e+12        -0.1440             0.2532   \n",
       "493    08-19  1.660838e+12        -0.1797             0.2261   \n",
       "511    08-02  1.659370e+12         0.2393             0.0472   \n",
       "514    07-29  1.659024e+12         0.1747             0.1376   \n",
       "\n",
       "     annual_return_this_year_x  annual_return_year  ...  half_a_year  \\\n",
       "11                      0.0897              0.1405  ...        False   \n",
       "23                      0.0900              0.1028  ...        False   \n",
       "24                     -0.0888             -0.2075  ...        False   \n",
       "60                     -0.0123              0.0157  ...        False   \n",
       "117                     0.0376              0.1335  ...        False   \n",
       "123                    -0.0190              0.0255  ...        False   \n",
       "161                    -0.1979             -0.1445  ...        False   \n",
       "181                    -0.2587             -0.1961  ...        False   \n",
       "185                    -0.2834             -0.3085  ...        False   \n",
       "223                    -0.1973             -0.2050  ...        False   \n",
       "231                    -0.0789             -0.1434  ...        False   \n",
       "241                    -0.2547             -0.2049  ...        False   \n",
       "242                     0.1046              0.1226  ...        False   \n",
       "243                    -0.1856             -0.1781  ...        False   \n",
       "246                    -0.2790             -0.2958  ...        False   \n",
       "277                     0.0283              0.0396  ...        False   \n",
       "285                    -0.0562             -0.1213  ...        False   \n",
       "357                    -0.0089              0.0170  ...        False   \n",
       "360                     0.0310              0.0867  ...        False   \n",
       "362                     0.0503              0.1071  ...        False   \n",
       "417                    -0.0005                 NaN  ...         True   \n",
       "420                    -0.0013                 NaN  ...         True   \n",
       "485                    -0.0404                 NaN  ...        False   \n",
       "493                    -0.2078                 NaN  ...        False   \n",
       "511                     0.0422              0.0424  ...        False   \n",
       "514                    -0.0613              0.0256  ...        False   \n",
       "\n",
       "                  crawltime name_y profit_rate_y annual_return_this_year_y  \\\n",
       "11  2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "23  2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "24  2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "60  2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "117 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "123 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "161 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "181 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "185 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "223 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "231 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "241 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "242 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "243 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "246 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "277 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "285 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "357 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "360 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "362 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "417 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "420 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "485 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "493 2022-08-24 22:23:29.646    NaN           NaN                       NaN   \n",
       "511 2022-08-24 22:23:41.368    NaN           NaN                       NaN   \n",
       "514 2022-08-24 22:23:41.368    NaN           NaN                       NaN   \n",
       "\n",
       "    manager_nick_name_y netvalue_date  create_date close_status crwaltime  \n",
       "11                  NaN           NaN          NaN          NaN       NaT  \n",
       "23                  NaN           NaN          NaN          NaN       NaT  \n",
       "24                  NaN           NaN          NaN          NaN       NaT  \n",
       "60                  NaN           NaN          NaN          NaN       NaT  \n",
       "117                 NaN           NaN          NaN          NaN       NaT  \n",
       "123                 NaN           NaN          NaN          NaN       NaT  \n",
       "161                 NaN           NaN          NaN          NaN       NaT  \n",
       "181                 NaN           NaN          NaN          NaN       NaT  \n",
       "185                 NaN           NaN          NaN          NaN       NaT  \n",
       "223                 NaN           NaN          NaN          NaN       NaT  \n",
       "231                 NaN           NaN          NaN          NaN       NaT  \n",
       "241                 NaN           NaN          NaN          NaN       NaT  \n",
       "242                 NaN           NaN          NaN          NaN       NaT  \n",
       "243                 NaN           NaN          NaN          NaN       NaT  \n",
       "246                 NaN           NaN          NaN          NaN       NaT  \n",
       "277                 NaN           NaN          NaN          NaN       NaT  \n",
       "285                 NaN           NaN          NaN          NaN       NaT  \n",
       "357                 NaN           NaN          NaN          NaN       NaT  \n",
       "360                 NaN           NaN          NaN          NaN       NaT  \n",
       "362                 NaN           NaN          NaN          NaN       NaT  \n",
       "417                 NaN           NaN          NaN          NaN       NaT  \n",
       "420                 NaN           NaN          NaN          NaN       NaT  \n",
       "485                 NaN           NaN          NaN          NaN       NaT  \n",
       "493                 NaN           NaN          NaN          NaN       NaT  \n",
       "511                 NaN           NaN          NaN          NaN       NaT  \n",
       "514                 NaN           NaN          NaN          NaN       NaT  \n",
       "\n",
       "[26 rows x 28 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_df[merge_df['create_date'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "60"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(merge_df[merge_df['create_date']>'2022-01-01'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>priority</th>\n",
       "      <th>symbol</th>\n",
       "      <th>name</th>\n",
       "      <th>manager_nick_name</th>\n",
       "      <th>unit_net_value</th>\n",
       "      <th>end_date</th>\n",
       "      <th>end_date_val</th>\n",
       "      <th>profit_rate</th>\n",
       "      <th>max_drawdown_rate</th>\n",
       "      <th>annual_return_this_year</th>\n",
       "      <th>...</th>\n",
       "      <th>show_profit_status</th>\n",
       "      <th>fd_code</th>\n",
       "      <th>has_select</th>\n",
       "      <th>risk_rating</th>\n",
       "      <th>is_open_day_tmp</th>\n",
       "      <th>is_raise</th>\n",
       "      <th>create_time</th>\n",
       "      <th>sharp_rate</th>\n",
       "      <th>half_a_year</th>\n",
       "      <th>crawltime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [priority, symbol, name, manager_nick_name, unit_net_value, end_date, end_date_val, profit_rate, max_drawdown_rate, annual_return_this_year, annual_return_year, show_profit_status, fd_code, has_select, risk_rating, is_open_day_tmp, is_raise, create_time, sharp_rate, half_a_year, crawltime]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 21 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['annual_return_this_year'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "238"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(merge_df[merge_df['close_status']==True])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "752"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(merge_df[merge_df['close_status']==False])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "238"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    752\n",
       "True     238\n",
       "Name: close_status, dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_df['close_status'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_20055/1612014277.py:1: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  merge_df[merge_df[merge_df['close_status']==False]['create_date']<'2022-01-01']\n"
     ]
    },
    {
     "ename": "IndexingError",
     "evalue": "Unalignable boolean Series provided as indexer (index of the boolean Series and of the indexed object do not match).",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mIndexingError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[1;32m/home/xda/github/stock/analysis/雪球私募收益率分析.ipynb Cell 37'\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> <a href='vscode-notebook-cell:/home/xda/github/stock/analysis/%E9%9B%AA%E7%90%83%E7%A7%81%E5%8B%9F%E6%94%B6%E7%9B%8A%E7%8E%87%E5%88%86%E6%9E%90.ipynb#ch0000036?line=0'>1</a>\u001b[0m merge_df[merge_df[merge_df[\u001b[39m'\u001b[39;49m\u001b[39mclose_status\u001b[39;49m\u001b[39m'\u001b[39;49m]\u001b[39m==\u001b[39;49m\u001b[39mFalse\u001b[39;49;00m][\u001b[39m'\u001b[39;49m\u001b[39mcreate_date\u001b[39;49m\u001b[39m'\u001b[39;49m]\u001b[39m<\u001b[39;49m\u001b[39m'\u001b[39;49m\u001b[39m2022-01-01\u001b[39;49m\u001b[39m'\u001b[39;49m]\n",
      "File \u001b[0;32m~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/frame.py:3496\u001b[0m, in \u001b[0;36mDataFrame.__getitem__\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/frame.py?line=3493'>3494</a>\u001b[0m \u001b[39m# Do we have a (boolean) 1d indexer?\u001b[39;00m\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/frame.py?line=3494'>3495</a>\u001b[0m \u001b[39mif\u001b[39;00m com\u001b[39m.\u001b[39mis_bool_indexer(key):\n\u001b[0;32m-> <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/frame.py?line=3495'>3496</a>\u001b[0m     \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_getitem_bool_array(key)\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/frame.py?line=3497'>3498</a>\u001b[0m \u001b[39m# We are left with two options: a single key, and a collection of keys,\u001b[39;00m\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/frame.py?line=3498'>3499</a>\u001b[0m \u001b[39m# We interpret tuples as collections only for non-MultiIndex\u001b[39;00m\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/frame.py?line=3499'>3500</a>\u001b[0m is_single_key \u001b[39m=\u001b[39m \u001b[39misinstance\u001b[39m(key, \u001b[39mtuple\u001b[39m) \u001b[39mor\u001b[39;00m \u001b[39mnot\u001b[39;00m is_list_like(key)\n",
      "File \u001b[0;32m~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/frame.py:3549\u001b[0m, in \u001b[0;36mDataFrame._getitem_bool_array\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/frame.py?line=3542'>3543</a>\u001b[0m     \u001b[39mraise\u001b[39;00m \u001b[39mValueError\u001b[39;00m(\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/frame.py?line=3543'>3544</a>\u001b[0m         \u001b[39mf\u001b[39m\u001b[39m\"\u001b[39m\u001b[39mItem wrong length \u001b[39m\u001b[39m{\u001b[39;00m\u001b[39mlen\u001b[39m(key)\u001b[39m}\u001b[39;00m\u001b[39m instead of \u001b[39m\u001b[39m{\u001b[39;00m\u001b[39mlen\u001b[39m(\u001b[39mself\u001b[39m\u001b[39m.\u001b[39mindex)\u001b[39m}\u001b[39;00m\u001b[39m.\u001b[39m\u001b[39m\"\u001b[39m\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/frame.py?line=3544'>3545</a>\u001b[0m     )\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/frame.py?line=3546'>3547</a>\u001b[0m \u001b[39m# check_bool_indexer will throw exception if Series key cannot\u001b[39;00m\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/frame.py?line=3547'>3548</a>\u001b[0m \u001b[39m# be reindexed to match DataFrame rows\u001b[39;00m\n\u001b[0;32m-> <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/frame.py?line=3548'>3549</a>\u001b[0m key \u001b[39m=\u001b[39m check_bool_indexer(\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mindex, key)\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/frame.py?line=3549'>3550</a>\u001b[0m indexer \u001b[39m=\u001b[39m key\u001b[39m.\u001b[39mnonzero()[\u001b[39m0\u001b[39m]\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/frame.py?line=3550'>3551</a>\u001b[0m \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_take_with_is_copy(indexer, axis\u001b[39m=\u001b[39m\u001b[39m0\u001b[39m)\n",
      "File \u001b[0;32m~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/indexing.py:2376\u001b[0m, in \u001b[0;36mcheck_bool_indexer\u001b[0;34m(index, key)\u001b[0m\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/indexing.py?line=2373'>2374</a>\u001b[0m     mask \u001b[39m=\u001b[39m isna(result\u001b[39m.\u001b[39m_values)\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/indexing.py?line=2374'>2375</a>\u001b[0m     \u001b[39mif\u001b[39;00m mask\u001b[39m.\u001b[39many():\n\u001b[0;32m-> <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/indexing.py?line=2375'>2376</a>\u001b[0m         \u001b[39mraise\u001b[39;00m IndexingError(\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/indexing.py?line=2376'>2377</a>\u001b[0m             \u001b[39m\"\u001b[39m\u001b[39mUnalignable boolean Series provided as \u001b[39m\u001b[39m\"\u001b[39m\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/indexing.py?line=2377'>2378</a>\u001b[0m             \u001b[39m\"\u001b[39m\u001b[39mindexer (index of the boolean Series and of \u001b[39m\u001b[39m\"\u001b[39m\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/indexing.py?line=2378'>2379</a>\u001b[0m             \u001b[39m\"\u001b[39m\u001b[39mthe indexed object do not match).\u001b[39m\u001b[39m\"\u001b[39m\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/indexing.py?line=2379'>2380</a>\u001b[0m         )\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/indexing.py?line=2380'>2381</a>\u001b[0m     \u001b[39mreturn\u001b[39;00m result\u001b[39m.\u001b[39mastype(\u001b[39mbool\u001b[39m)\u001b[39m.\u001b[39m_values\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/indexing.py?line=2381'>2382</a>\u001b[0m \u001b[39mif\u001b[39;00m is_object_dtype(key):\n\u001b[1;32m   <a href='file:///~/miniconda3/envs/cpy/lib/python3.9/site-packages/pandas/core/indexing.py?line=2382'>2383</a>\u001b[0m     \u001b[39m# key might be object-dtype bool, check_array_indexer needs bool array\u001b[39;00m\n",
      "\u001b[0;31mIndexingError\u001b[0m: Unalignable boolean Series provided as indexer (index of the boolean Series and of the indexed object do not match)."
     ]
    }
   ],
   "source": [
    "merge_df[merge_df[merge_df['close_status']==False]['create_date']<'2022-01-01']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "t1=merge_df[merge_df['close_status']==False]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "692"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(t1[t1['create_date']<'2022-01-01'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 752 entries, 0 to 1015\n",
      "Data columns (total 28 columns):\n",
      " #   Column                     Non-Null Count  Dtype         \n",
      "---  ------                     --------------  -----         \n",
      " 0   symbol                     752 non-null    object        \n",
      " 1   name_x                     494 non-null    object        \n",
      " 2   manager_nick_name_x        475 non-null    object        \n",
      " 3   unit_net_value             494 non-null    float64       \n",
      " 4   end_date                   494 non-null    object        \n",
      " 5   end_date_val               494 non-null    float64       \n",
      " 6   profit_rate_x              494 non-null    float64       \n",
      " 7   max_drawdown_rate          494 non-null    float64       \n",
      " 8   annual_return_this_year_x  494 non-null    float64       \n",
      " 9   annual_return_year         410 non-null    float64       \n",
      " 10  show_profit_status         494 non-null    float64       \n",
      " 11  fd_code                    494 non-null    object        \n",
      " 12  has_select                 494 non-null    object        \n",
      " 13  risk_rating                0 non-null      object        \n",
      " 14  is_open_day_tmp            0 non-null      object        \n",
      " 15  is_raise                   0 non-null      object        \n",
      " 16  create_time                0 non-null      object        \n",
      " 17  sharp_rate                 466 non-null    float64       \n",
      " 18  half_a_year                494 non-null    object        \n",
      " 19  crawltime                  494 non-null    datetime64[ns]\n",
      " 20  name_y                     752 non-null    object        \n",
      " 21  profit_rate_y              672 non-null    float64       \n",
      " 22  annual_return_this_year_y  589 non-null    float64       \n",
      " 23  manager_nick_name_y        310 non-null    object        \n",
      " 24  netvalue_date              752 non-null    object        \n",
      " 25  create_date                752 non-null    object        \n",
      " 26  close_status               752 non-null    object        \n",
      " 27  crwaltime                  752 non-null    datetime64[ns]\n",
      "dtypes: datetime64[ns](2), float64(10), object(16)\n",
      "memory usage: 170.4+ KB\n"
     ]
    }
   ],
   "source": [
    "t1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>symbol</th>\n",
       "      <th>name_x</th>\n",
       "      <th>manager_nick_name_x</th>\n",
       "      <th>unit_net_value</th>\n",
       "      <th>end_date</th>\n",
       "      <th>end_date_val</th>\n",
       "      <th>profit_rate_x</th>\n",
       "      <th>max_drawdown_rate</th>\n",
       "      <th>annual_return_this_year_x</th>\n",
       "      <th>annual_return_year</th>\n",
       "      <th>...</th>\n",
       "      <th>half_a_year</th>\n",
       "      <th>crawltime</th>\n",
       "      <th>name_y</th>\n",
       "      <th>profit_rate_y</th>\n",
       "      <th>annual_return_this_year_y</th>\n",
       "      <th>manager_nick_name_y</th>\n",
       "      <th>netvalue_date</th>\n",
       "      <th>create_date</th>\n",
       "      <th>close_status</th>\n",
       "      <th>crwaltime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>570</th>\n",
       "      <td>P000145</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>私募工场(牛金)</td>\n",
       "      <td>100.70</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-11-29</td>\n",
       "      <td>2014-12-23</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 16:49:57.213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>627</th>\n",
       "      <td>P000220</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>沐海基金</td>\n",
       "      <td>66.39</td>\n",
       "      <td>-19.98</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-08-15</td>\n",
       "      <td>2016-01-07</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 16:50:46.413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>628</th>\n",
       "      <td>P000223</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>朴信起航一号</td>\n",
       "      <td>59.56</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-09-10</td>\n",
       "      <td>2016-02-02</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 16:50:48.161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>631</th>\n",
       "      <td>P000230</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>私募工场鼎杉1期</td>\n",
       "      <td>12.84</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-12-07</td>\n",
       "      <td>2014-08-28</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 16:50:52.007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>637</th>\n",
       "      <td>P000236</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>私募工场兴富进取1期</td>\n",
       "      <td>200.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>广东兴富投资</td>\n",
       "      <td>2017-07-21</td>\n",
       "      <td>2014-07-11</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 16:50:59.576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1011</th>\n",
       "      <td>P001231</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>衍复雪瑞500指增1期</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>2022-08-22</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 17:05:06.779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1012</th>\n",
       "      <td>P001233</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>稳博壬寅1000指数增强1－6号</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>稳博投资</td>\n",
       "      <td></td>\n",
       "      <td>2022-08-25</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 17:05:07.763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1013</th>\n",
       "      <td>P001248</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>展弘多策略21号</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>2022-08-24</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 17:05:18.894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1014</th>\n",
       "      <td>P001255</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>广金美好玻色1期</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>2022-08-22</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 17:05:31.547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1015</th>\n",
       "      <td>P001268</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>广金美好康德多策略1期</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>2022-09-14</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 17:05:42.691</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>258 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       symbol name_x manager_nick_name_x  unit_net_value end_date  \\\n",
       "570   P000145    NaN                 NaN             NaN      NaN   \n",
       "627   P000220    NaN                 NaN             NaN      NaN   \n",
       "628   P000223    NaN                 NaN             NaN      NaN   \n",
       "631   P000230    NaN                 NaN             NaN      NaN   \n",
       "637   P000236    NaN                 NaN             NaN      NaN   \n",
       "...       ...    ...                 ...             ...      ...   \n",
       "1011  P001231    NaN                 NaN             NaN      NaN   \n",
       "1012  P001233    NaN                 NaN             NaN      NaN   \n",
       "1013  P001248    NaN                 NaN             NaN      NaN   \n",
       "1014  P001255    NaN                 NaN             NaN      NaN   \n",
       "1015  P001268    NaN                 NaN             NaN      NaN   \n",
       "\n",
       "      end_date_val  profit_rate_x  max_drawdown_rate  \\\n",
       "570            NaN            NaN                NaN   \n",
       "627            NaN            NaN                NaN   \n",
       "628            NaN            NaN                NaN   \n",
       "631            NaN            NaN                NaN   \n",
       "637            NaN            NaN                NaN   \n",
       "...            ...            ...                ...   \n",
       "1011           NaN            NaN                NaN   \n",
       "1012           NaN            NaN                NaN   \n",
       "1013           NaN            NaN                NaN   \n",
       "1014           NaN            NaN                NaN   \n",
       "1015           NaN            NaN                NaN   \n",
       "\n",
       "      annual_return_this_year_x  annual_return_year  ...  half_a_year  \\\n",
       "570                         NaN                 NaN  ...          NaN   \n",
       "627                         NaN                 NaN  ...          NaN   \n",
       "628                         NaN                 NaN  ...          NaN   \n",
       "631                         NaN                 NaN  ...          NaN   \n",
       "637                         NaN                 NaN  ...          NaN   \n",
       "...                         ...                 ...  ...          ...   \n",
       "1011                        NaN                 NaN  ...          NaN   \n",
       "1012                        NaN                 NaN  ...          NaN   \n",
       "1013                        NaN                 NaN  ...          NaN   \n",
       "1014                        NaN                 NaN  ...          NaN   \n",
       "1015                        NaN                 NaN  ...          NaN   \n",
       "\n",
       "     crawltime            name_y profit_rate_y annual_return_this_year_y  \\\n",
       "570        NaT          私募工场(牛金)        100.70                       NaN   \n",
       "627        NaT              沐海基金         66.39                    -19.98   \n",
       "628        NaT            朴信起航一号         59.56                       NaN   \n",
       "631        NaT          私募工场鼎杉1期         12.84                       NaN   \n",
       "637        NaT        私募工场兴富进取1期        200.00                       NaN   \n",
       "...        ...               ...           ...                       ...   \n",
       "1011       NaT       衍复雪瑞500指增1期           NaN                       NaN   \n",
       "1012       NaT  稳博壬寅1000指数增强1－6号           NaN                       NaN   \n",
       "1013       NaT          展弘多策略21号           NaN                       NaN   \n",
       "1014       NaT          广金美好玻色1期           NaN                       NaN   \n",
       "1015       NaT       广金美好康德多策略1期           NaN                       NaN   \n",
       "\n",
       "     manager_nick_name_y netvalue_date  create_date close_status  \\\n",
       "570                 None    2019-11-29   2014-12-23        False   \n",
       "627                 None    2022-08-15   2016-01-07        False   \n",
       "628                 None    2021-09-10   2016-02-02        False   \n",
       "631                 None    2018-12-07   2014-08-28        False   \n",
       "637               广东兴富投资    2017-07-21   2014-07-11        False   \n",
       "...                  ...           ...          ...          ...   \n",
       "1011                None                 2022-08-22        False   \n",
       "1012                稳博投资                 2022-08-25        False   \n",
       "1013                None                 2022-08-24        False   \n",
       "1014                None                 2022-08-22        False   \n",
       "1015                None                 2022-09-14        False   \n",
       "\n",
       "                   crwaltime  \n",
       "570  2022-08-18 16:49:57.213  \n",
       "627  2022-08-18 16:50:46.413  \n",
       "628  2022-08-18 16:50:48.161  \n",
       "631  2022-08-18 16:50:52.007  \n",
       "637  2022-08-18 16:50:59.576  \n",
       "...                      ...  \n",
       "1011 2022-08-18 17:05:06.779  \n",
       "1012 2022-08-18 17:05:07.763  \n",
       "1013 2022-08-18 17:05:18.894  \n",
       "1014 2022-08-18 17:05:31.547  \n",
       "1015 2022-08-18 17:05:42.691  \n",
       "\n",
       "[258 rows x 28 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t1[t1['annual_return_this_year_x'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>symbol</th>\n",
       "      <th>name_x</th>\n",
       "      <th>manager_nick_name_x</th>\n",
       "      <th>unit_net_value</th>\n",
       "      <th>end_date</th>\n",
       "      <th>end_date_val</th>\n",
       "      <th>profit_rate_x</th>\n",
       "      <th>max_drawdown_rate</th>\n",
       "      <th>annual_return_this_year_x</th>\n",
       "      <th>annual_return_year</th>\n",
       "      <th>...</th>\n",
       "      <th>half_a_year</th>\n",
       "      <th>crawltime</th>\n",
       "      <th>name_y</th>\n",
       "      <th>profit_rate_y</th>\n",
       "      <th>annual_return_this_year_y</th>\n",
       "      <th>manager_nick_name_y</th>\n",
       "      <th>netvalue_date</th>\n",
       "      <th>create_date</th>\n",
       "      <th>close_status</th>\n",
       "      <th>crwaltime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>570</th>\n",
       "      <td>P000145</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>私募工场(牛金)</td>\n",
       "      <td>100.70</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-11-29</td>\n",
       "      <td>2014-12-23</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 16:49:57.213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>628</th>\n",
       "      <td>P000223</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>朴信起航一号</td>\n",
       "      <td>59.56</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2021-09-10</td>\n",
       "      <td>2016-02-02</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 16:50:48.161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>631</th>\n",
       "      <td>P000230</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>私募工场鼎杉1期</td>\n",
       "      <td>12.84</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2018-12-07</td>\n",
       "      <td>2014-08-28</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 16:50:52.007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>637</th>\n",
       "      <td>P000236</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>私募工场兴富进取1期</td>\n",
       "      <td>200.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>广东兴富投资</td>\n",
       "      <td>2017-07-21</td>\n",
       "      <td>2014-07-11</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 16:50:59.576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>647</th>\n",
       "      <td>P000267</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>私募工场麟悦1期</td>\n",
       "      <td>39.46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>2020-09-18</td>\n",
       "      <td>2016-03-28</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 16:51:40.217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1011</th>\n",
       "      <td>P001231</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>衍复雪瑞500指增1期</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>2022-08-22</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 17:05:06.779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1012</th>\n",
       "      <td>P001233</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>稳博壬寅1000指数增强1－6号</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>稳博投资</td>\n",
       "      <td></td>\n",
       "      <td>2022-08-25</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 17:05:07.763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1013</th>\n",
       "      <td>P001248</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>展弘多策略21号</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>2022-08-24</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 17:05:18.894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1014</th>\n",
       "      <td>P001255</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>广金美好玻色1期</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>2022-08-22</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 17:05:31.547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1015</th>\n",
       "      <td>P001268</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>广金美好康德多策略1期</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>2022-09-14</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-18 17:05:42.691</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>159 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       symbol name_x manager_nick_name_x  unit_net_value end_date  \\\n",
       "570   P000145    NaN                 NaN             NaN      NaN   \n",
       "628   P000223    NaN                 NaN             NaN      NaN   \n",
       "631   P000230    NaN                 NaN             NaN      NaN   \n",
       "637   P000236    NaN                 NaN             NaN      NaN   \n",
       "647   P000267    NaN                 NaN             NaN      NaN   \n",
       "...       ...    ...                 ...             ...      ...   \n",
       "1011  P001231    NaN                 NaN             NaN      NaN   \n",
       "1012  P001233    NaN                 NaN             NaN      NaN   \n",
       "1013  P001248    NaN                 NaN             NaN      NaN   \n",
       "1014  P001255    NaN                 NaN             NaN      NaN   \n",
       "1015  P001268    NaN                 NaN             NaN      NaN   \n",
       "\n",
       "      end_date_val  profit_rate_x  max_drawdown_rate  \\\n",
       "570            NaN            NaN                NaN   \n",
       "628            NaN            NaN                NaN   \n",
       "631            NaN            NaN                NaN   \n",
       "637            NaN            NaN                NaN   \n",
       "647            NaN            NaN                NaN   \n",
       "...            ...            ...                ...   \n",
       "1011           NaN            NaN                NaN   \n",
       "1012           NaN            NaN                NaN   \n",
       "1013           NaN            NaN                NaN   \n",
       "1014           NaN            NaN                NaN   \n",
       "1015           NaN            NaN                NaN   \n",
       "\n",
       "      annual_return_this_year_x  annual_return_year  ...  half_a_year  \\\n",
       "570                         NaN                 NaN  ...          NaN   \n",
       "628                         NaN                 NaN  ...          NaN   \n",
       "631                         NaN                 NaN  ...          NaN   \n",
       "637                         NaN                 NaN  ...          NaN   \n",
       "647                         NaN                 NaN  ...          NaN   \n",
       "...                         ...                 ...  ...          ...   \n",
       "1011                        NaN                 NaN  ...          NaN   \n",
       "1012                        NaN                 NaN  ...          NaN   \n",
       "1013                        NaN                 NaN  ...          NaN   \n",
       "1014                        NaN                 NaN  ...          NaN   \n",
       "1015                        NaN                 NaN  ...          NaN   \n",
       "\n",
       "     crawltime            name_y profit_rate_y annual_return_this_year_y  \\\n",
       "570        NaT          私募工场(牛金)        100.70                       NaN   \n",
       "628        NaT            朴信起航一号         59.56                       NaN   \n",
       "631        NaT          私募工场鼎杉1期         12.84                       NaN   \n",
       "637        NaT        私募工场兴富进取1期        200.00                       NaN   \n",
       "647        NaT          私募工场麟悦1期         39.46                       NaN   \n",
       "...        ...               ...           ...                       ...   \n",
       "1011       NaT       衍复雪瑞500指增1期           NaN                       NaN   \n",
       "1012       NaT  稳博壬寅1000指数增强1－6号           NaN                       NaN   \n",
       "1013       NaT          展弘多策略21号           NaN                       NaN   \n",
       "1014       NaT          广金美好玻色1期           NaN                       NaN   \n",
       "1015       NaT       广金美好康德多策略1期           NaN                       NaN   \n",
       "\n",
       "     manager_nick_name_y netvalue_date  create_date close_status  \\\n",
       "570                 None    2019-11-29   2014-12-23        False   \n",
       "628                 None    2021-09-10   2016-02-02        False   \n",
       "631                 None    2018-12-07   2014-08-28        False   \n",
       "637               广东兴富投资    2017-07-21   2014-07-11        False   \n",
       "647                 None    2020-09-18   2016-03-28        False   \n",
       "...                  ...           ...          ...          ...   \n",
       "1011                None                 2022-08-22        False   \n",
       "1012                稳博投资                 2022-08-25        False   \n",
       "1013                None                 2022-08-24        False   \n",
       "1014                None                 2022-08-22        False   \n",
       "1015                None                 2022-09-14        False   \n",
       "\n",
       "                   crwaltime  \n",
       "570  2022-08-18 16:49:57.213  \n",
       "628  2022-08-18 16:50:48.161  \n",
       "631  2022-08-18 16:50:52.007  \n",
       "637  2022-08-18 16:50:59.576  \n",
       "647  2022-08-18 16:51:40.217  \n",
       "...                      ...  \n",
       "1011 2022-08-18 17:05:06.779  \n",
       "1012 2022-08-18 17:05:07.763  \n",
       "1013 2022-08-18 17:05:18.894  \n",
       "1014 2022-08-18 17:05:31.547  \n",
       "1015 2022-08-18 17:05:42.691  \n",
       "\n",
       "[159 rows x 28 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t1[t1['netvalue_date']<'2022-01-01']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "520"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>_id</th>\n",
       "      <th>priority</th>\n",
       "      <th>symbol</th>\n",
       "      <th>name</th>\n",
       "      <th>manager_nick_name</th>\n",
       "      <th>unit_net_value</th>\n",
       "      <th>end_date</th>\n",
       "      <th>end_date_val</th>\n",
       "      <th>profit_rate</th>\n",
       "      <th>...</th>\n",
       "      <th>show_profit_status</th>\n",
       "      <th>fd_code</th>\n",
       "      <th>has_select</th>\n",
       "      <th>risk_rating</th>\n",
       "      <th>is_open_day_tmp</th>\n",
       "      <th>is_raise</th>\n",
       "      <th>create_time</th>\n",
       "      <th>sharp_rate</th>\n",
       "      <th>half_a_year</th>\n",
       "      <th>crawltime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>411</td>\n",
       "      <td>6306346ae1b8ad401d34c7e1</td>\n",
       "      <td>-1</td>\n",
       "      <td>P001038</td>\n",
       "      <td>珠池景元雪球相对价值</td>\n",
       "      <td>珠池资产</td>\n",
       "      <td>1.0008</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1660838400000</td>\n",
       "      <td>0.0009</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>STL168</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>412</td>\n",
       "      <td>6306346ae1b8ad401d34c7e2</td>\n",
       "      <td>-1</td>\n",
       "      <td>P001166</td>\n",
       "      <td>SAP智龙雪球CTA</td>\n",
       "      <td>深圳前海智龙资本</td>\n",
       "      <td>1.0009</td>\n",
       "      <td>08-19</td>\n",
       "      <td>1660838400000</td>\n",
       "      <td>0.0009</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>SVJ630</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>413</td>\n",
       "      <td>6306346ae1b8ad401d34c7e3</td>\n",
       "      <td>-1</td>\n",
       "      <td>P001172</td>\n",
       "      <td>鸿道创新改革尊享6号</td>\n",
       "      <td>鸿道投资</td>\n",
       "      <td>1.0008</td>\n",
       "      <td>08-22</td>\n",
       "      <td>1661097600000</td>\n",
       "      <td>0.0007</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>SVK522</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>2022-08-24 22:23:29.646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>511</td>\n",
       "      <td>6306346de1b8ad401d34c845</td>\n",
       "      <td>-1</td>\n",
       "      <td>P666005</td>\n",
       "      <td>华宝证券华量安瑞1号FOF集合资产管理计划</td>\n",
       "      <td>华宝资管总部</td>\n",
       "      <td>1.2393</td>\n",
       "      <td>08-02</td>\n",
       "      <td>1659369600000</td>\n",
       "      <td>0.2393</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>E20112</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1.09</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:41.368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>515</td>\n",
       "      <td>6306346fe1b8ad401d34c849</td>\n",
       "      <td>-1</td>\n",
       "      <td>P001264</td>\n",
       "      <td>素养鸣石量化18号</td>\n",
       "      <td>鸣石投资</td>\n",
       "      <td>1.1735</td>\n",
       "      <td>08-05</td>\n",
       "      <td>1659628800000</td>\n",
       "      <td>0.5080</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>SCX293</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1.24</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-08-24 22:23:41.368</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     index                       _id  priority   symbol  \\\n",
       "139    411  6306346ae1b8ad401d34c7e1        -1  P001038   \n",
       "140    412  6306346ae1b8ad401d34c7e2        -1  P001166   \n",
       "141    413  6306346ae1b8ad401d34c7e3        -1  P001172   \n",
       "142    511  6306346de1b8ad401d34c845        -1  P666005   \n",
       "143    515  6306346fe1b8ad401d34c849        -1  P001264   \n",
       "\n",
       "                      name manager_nick_name  unit_net_value end_date  \\\n",
       "139             珠池景元雪球相对价值              珠池资产          1.0008    08-19   \n",
       "140             SAP智龙雪球CTA          深圳前海智龙资本          1.0009    08-19   \n",
       "141             鸿道创新改革尊享6号              鸿道投资          1.0008    08-22   \n",
       "142  华宝证券华量安瑞1号FOF集合资产管理计划            华宝资管总部          1.2393    08-02   \n",
       "143              素养鸣石量化18号              鸣石投资          1.1735    08-05   \n",
       "\n",
       "      end_date_val  profit_rate  ...  show_profit_status  fd_code  has_select  \\\n",
       "139  1660838400000       0.0009  ...                   0   STL168       False   \n",
       "140  1660838400000       0.0009  ...                   0   SVJ630       False   \n",
       "141  1661097600000       0.0007  ...                   0   SVK522       False   \n",
       "142  1659369600000       0.2393  ...                   0   E20112       False   \n",
       "143  1659628800000       0.5080  ...                   0   SCX293       False   \n",
       "\n",
       "     risk_rating is_open_day_tmp  is_raise create_time sharp_rate half_a_year  \\\n",
       "139         None            None      None        None        NaN        True   \n",
       "140         None            None      None        None        NaN        True   \n",
       "141         None            None      None        None        NaN        True   \n",
       "142         None            None      None        None       1.09       False   \n",
       "143         None            None      None        None       1.24       False   \n",
       "\n",
       "                  crawltime  \n",
       "139 2022-08-24 22:23:29.646  \n",
       "140 2022-08-24 22:23:29.646  \n",
       "141 2022-08-24 22:23:29.646  \n",
       "142 2022-08-24 22:23:41.368  \n",
       "143 2022-08-24 22:23:41.368  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['annual_return_this_year']>0].reset_index().tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m/home/xda/github/stock/analysis/雪球私募收益率分析.ipynb Cell 45'\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> <a href='vscode-notebook-cell:/home/xda/github/stock/analysis/%E9%9B%AA%E7%90%83%E7%A7%81%E5%8B%9F%E6%94%B6%E7%9B%8A%E7%8E%87%E5%88%86%E6%9E%90.ipynb#ch0000044?line=0'>1</a>\u001b[0m \u001b[39mlen\u001b[39m(df)\n",
      "\u001b[0;31mNameError\u001b[0m: name 'df' is not defined"
     ]
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel('xueqiu_private_fund.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['rank']=df['annual_return_this_year'].rank(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>希瓦小牛1号</td>\n",
       "      <td>390.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>希瓦小牛3号</td>\n",
       "      <td>393.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>希瓦大牛1号</td>\n",
       "      <td>355.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>希瓦辉耀1号</td>\n",
       "      <td>385.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>希瓦大牛2号</td>\n",
       "      <td>384.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>269</th>\n",
       "      <td>希瓦小牛雪球</td>\n",
       "      <td>357.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>319</th>\n",
       "      <td>希瓦小牛17号</td>\n",
       "      <td>381.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>341</th>\n",
       "      <td>希瓦小牛雪球2号</td>\n",
       "      <td>386.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>471</th>\n",
       "      <td>希瓦小牛15号</td>\n",
       "      <td>367.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>483</th>\n",
       "      <td>希瓦刀塔FOF1号</td>\n",
       "      <td>335.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497</th>\n",
       "      <td>希瓦大牛7号</td>\n",
       "      <td>343.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499</th>\n",
       "      <td>希瓦大牛5号</td>\n",
       "      <td>330.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>501</th>\n",
       "      <td>希瓦刀塔FOF2号</td>\n",
       "      <td>326.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>504</th>\n",
       "      <td>希瓦大牛3号</td>\n",
       "      <td>329.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>507</th>\n",
       "      <td>希瓦辉耀2号</td>\n",
       "      <td>339.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          name   rank\n",
       "4       希瓦小牛1号  390.0\n",
       "16      希瓦小牛3号  393.0\n",
       "95      希瓦大牛1号  355.5\n",
       "188     希瓦辉耀1号  385.0\n",
       "251     希瓦大牛2号  384.0\n",
       "269     希瓦小牛雪球  357.5\n",
       "319    希瓦小牛17号  381.0\n",
       "341   希瓦小牛雪球2号  386.0\n",
       "471    希瓦小牛15号  367.0\n",
       "483  希瓦刀塔FOF1号  335.0\n",
       "497     希瓦大牛7号  343.0\n",
       "499     希瓦大牛5号  330.0\n",
       "501  希瓦刀塔FOF2号  326.0\n",
       "504     希瓦大牛3号  329.0\n",
       "507     希瓦辉耀2号  339.0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['manager_nick_name']=='梁宏'][['name','rank']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "414    145.0\n",
       "Name: rank, dtype: float64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['annual_return_this_year']==0]['rank']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-6.254499999999999"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['annual_return_this_year'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df['manager_nick_name']=='梁宏'][['name','manager_nick_name','this_year_actual_return']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-0.20125"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['manager_nick_name']=='梁宏'][['name','manager_nick_name','this_year_actual_return']]['this_year_actual_return'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.3988125"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['manager_nick_name']=='梁宏']['max_drawdown_rate'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "906064a35c7980fda78a8b97014bc5560dca56efd2e047844bdf4fb8a9b9c8a3"
  },
  "kernelspec": {
   "display_name": "Python 3.9.7 ('cpy')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
